package model.dao;

import java.io.File;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import model.SQLAdapter;
import model.bean.Tela;

public class TelaDAO {
	private static final String SQL_SELECT_QUANTIDADE_ACESSO = "SELECT COUNT(t.nome) AS count, t.nome FROM tela t INNER JOIN app a WHERE t.idsessao = a.idsessao AND a.nome = ? AND t.estado = 'start' AND t.periodo >= ? AND t.periodo <= ? GROUP BY t.nome ORDER BY t.nome ASC";
	private static final String SQL_SELECT_TEMPO_MEDIO = "SELECT t.nome, TIME_FORMAT(SEC_TO_TIME(SUM(TIME_TO_SEC(a.duracao))/COUNT(a.duracao)), '%H:%i:%s') AS media FROM tela t INNER JOIN app a WHERE t.idsessao = a.idsessao AND a.nome = ? AND t.estado = 'finish' AND t.duracao > 0 AND t.periodo >= ? AND t.periodo <= ? GROUP BY t.nome ORDER BY t.nome ASC";
	private static SQLAdapter sqlAdapter;
	
    private static SQLAdapter getSqlAdapter(){    	
    	if(sqlAdapter == null){
    		sqlAdapter = new SQLAdapter(new File("bd/bd_config.xml"));
    	}
		return sqlAdapter;
	}
    
	public static void setSqlAdapter(String driver, String protocol, String host, String database, String username, String password){
		if(getSqlAdapter() == null){
			sqlAdapter = new SQLAdapter(driver, protocol, host, database, username, password);			
		}else{
			getSqlAdapter().getDataSource().changeConnection(driver, protocol, host, database, username, password);
		}
	}
	
	public static void setSqlAdapter(File configFile){
		if(getSqlAdapter() == null){
			sqlAdapter = new SQLAdapter(configFile);			
		}else{
			getSqlAdapter().getDataSource().changeConnection(configFile);
		}
	}
	
	public static List<Tela> queryQuantidadeAcesso(String aplicacaoNome, String startPeriod, String endPeriod) throws SQLException, NullPointerException{
    	if(getSqlAdapter() != null){
    		PreparedStatement ps = getSqlAdapter().getPreparedStatement(SQL_SELECT_QUANTIDADE_ACESSO);
            if(ps != null){
            	List<Tela> telaList = new ArrayList<Tela>();
            	
            	ps.setString(1, aplicacaoNome);
            	ps.setString(2, startPeriod);
               	ps.setString(3, endPeriod);
               	
        		ResultSet resultSet = ps.executeQuery();
        		while(resultSet.next()){
        			telaList.add(new Tela(null, null, resultSet.getString("t.nome"), resultSet.getString("count"), null));
        		}
        		getSqlAdapter().close();
                
        		return telaList;
            }else{
            	throw new NullPointerException("PreparedStatement is null");
            }
    	}else{
    		throw new NullPointerException("The SqlAdapter is null, try to setSqlAdapter() before call to query.");
    	}
    }

	public static List<Tela> queryTempoMedio(String aplicacaoNome, String startPeriod, String endPeriod) throws SQLException, NullPointerException{
		if(getSqlAdapter() != null){
			List<Tela> telas = new ArrayList<Tela>();
			
			PreparedStatement ps = getSqlAdapter().getPreparedStatement(SQL_SELECT_TEMPO_MEDIO);
            if(ps != null){    			
               	ps.setString(1, aplicacaoNome);
               	ps.setString(2, startPeriod);
               	ps.setString(3, endPeriod);
               	
               	ResultSet resultSet = ps.executeQuery();
        		while(resultSet.next()){
        			telas.add(new Tela(null, null, resultSet.getString("t.nome"), null, resultSet.getTime("media")));
        		}
        		getSqlAdapter().close();
                
        		return telas;
            }else{
            	throw new NullPointerException("PreparedStatement is null");
            }
    	}else{
    		throw new NullPointerException("The SqlAdapter is null, try to setSqlAdapter() before call to query.");
    	}
    }
	
    public static void main(String[] args){
    	try{
			List<Tela> telaList = queryQuantidadeAcesso("Tvilo Viewer", "2012-8-7", "2012-11-1");
			for(int i=0; i<telaList.size(); i++){
				System.out.println(telaList.get(i));
			}
			
			System.out.println();
			
			List<Tela> telaList2 = queryTempoMedio("Tvilo Viewer", "2012-8-7", "2012-11-1");
			for(int i=0; i<telaList2.size(); i++){
				System.out.println(telaList2.get(i));
			}
			
			System.out.println("DONE");
			
		}catch(NullPointerException e){
			e.printStackTrace();
		}catch(SQLException e){
			e.printStackTrace();
		}
	}
}